﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SuperMarketSystem
{
    public partial class InForm : Form
    {
        public InForm()
        {
            InitializeComponent();
            fillIn();//填充全部进货记录
        }

        private void btnAddIn_Click(object sender, EventArgs e)//添加事件
        {
            TSM tsm = new TSM();//新建一个记录类
            Form tableForm = new TSMGet(ref tsm);//新建一个表格项目
            tableForm.ShowDialog();//接收记录项目
            if (tsm.ID != -1)//判断是否接收到了记录
            {
                bool isSelect = false;
                int isUpdate = 1;
                String sql1 = "insert into inlist(inID,itemID,inprice,innum,insum,inroot,indate)" +
                    " values(@ID,@ItemID,@Price,@Num,@Sum,@Root,@Date)";//数据库插入语句
                try
                {//一系列数据库操作
                    SqlConnection connection = MySqlServerUtils.getMySqlServerConnection();
                    SqlConnection connection2 = MySqlServerUtils.getMySqlServerConnection();
                    SqlConnection connection3 = MySqlServerUtils.getMySqlServerConnection();
                    SqlCommand command1 = new SqlCommand(sql1, connection);
                    command1.Parameters.AddWithValue("ID", tsm.ID);
                    command1.Parameters.AddWithValue("ItemID", tsm.itemID);
                    command1.Parameters.AddWithValue("Price", tsm.price);
                    command1.Parameters.AddWithValue("Num", tsm.num);
                    command1.Parameters.AddWithValue("Sum", tsm.sum);
                    command1.Parameters.AddWithValue("Root", tsm.sum);
                    command1.Parameters.AddWithValue("Date", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    String sql2 = String.Format("select * from stores where itemID={0}", tsm.itemID);
                    String sql3 = String.Format("update stores set itemstore=itemstore+{0} where itemID={1}",tsm.num,tsm.itemID);
                    SqlCommand command2 = new SqlCommand(sql2, connection2);
                    SqlCommand command3 = new SqlCommand(sql3, connection3);
                    SqlDataReader mySqlDataReader= command2.ExecuteReader();
                    isSelect = mySqlDataReader.Read();
                    mySqlDataReader.Close();
                    isUpdate=command3.ExecuteNonQuery();
                    if (isSelect != false && isUpdate != 0)//判断是否能在查询到商品ID的情况下也更新库存
                    {
                        command1.ExecuteNonQuery();//成功了则添加进货记录
                    }
                    else
                    {
                        MessageBox.Show("操作回滚", "书库里没有相关ID信息，无法更新");
                    }
                    MySqlServerUtils.closeSqlServerConnection(connection);
                    MySqlServerUtils.closeSqlServerConnection(connection2);
                    MySqlServerUtils.closeSqlServerConnection(connection3);
                }
                catch (Exception e2)
                {
                    
                }
                fillIn();
            }
        }
        private void fillIn()//填充进货记录表格
        {
            In.Items.Clear();
            String[] tbStrings = { "inID", "itemID", "inprice", "innum", "insum", "inroot", "indate" };
            SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection(); ;
            String smCommand = "select * from inlist";
            SqlCommand commandLogin = new SqlCommand(smCommand, connectionLogin);
            SqlDataReader smdr = commandLogin.ExecuteReader();
            while (smdr.Read())
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = smdr[tbStrings[0]].ToString();
                for (int i = 1; i < In.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                }
                In.Items.Add(listToAdd);
            }
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }

        private void btnCalIn_Click(object sender, EventArgs e)//统计进货记录
        {
            String dateString=DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss");
            TimePick timePick = new TimePick(ref dateString);
            timePick.ShowDialog();
            dateString = timePick.returnTime().ToString("yyyy-MM-dd HH:mm:ss");
            String sql =String.Format( "SELECT * FROM (SELECT * FROM inlist WHERE MONTH(indate) = MONTH('{0}') AND YEAR(indate)=YEAR('{1}'))a",dateString,dateString);
            In.Items.Clear();
            String[] tbStrings = { "inID", "itemID", "inprice", "innum", "insum", "inroot", "indate" };
            SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection(); ;//数据库连接获取
            SqlCommand commandLogin = new SqlCommand(sql, connectionLogin);
            SqlDataReader smdr = commandLogin.ExecuteReader();
            int numSum=0;
            double sumSum=0;
            while (smdr.Read())//读取满足条件的进货记录 即在指定月份内的
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = smdr[tbStrings[0]].ToString();
                for (int i = 1; i < In.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                }
                numSum += Convert.ToInt32(smdr["innum"]);
                sumSum += Convert.ToDouble(smdr["insum"]);
                In.Items.Add(listToAdd);
            }
            smdr.Close();
            String sql2 = String.Format("SELECT SUM(innum) as in_sum,SUM(insum) as sum_sum FROM (SELECT * FROM inlist WHERE MONTH(indate) = MONTH('{0}') AND YEAR(indate)=YEAR('{1}'))a", dateString, dateString);
            SqlCommand commandLogin2 = new SqlCommand(sql2, connectionLogin);
            SqlDataReader smdr2 = commandLogin2.ExecuteReader();
            if (smdr2.Read())
            {
                if (smdr2["in_sum"] != DBNull.Value)
                    numSum = Convert.ToInt32(smdr2["in_sum"]);
                if (smdr2["sum_sum"] != DBNull.Value)
                    sumSum = Convert.ToDouble(smdr2["sum_sum"]);
            }
            String lastAdd = timePick.returnTime().ToString("yyyy年--MM月");//规范化日期字符串
            ListViewItem listToAddNeo = new ListViewItem();//添加一行表格
            listToAddNeo.Text = "统计时间：";
            listToAddNeo.SubItems.Add(lastAdd);
            listToAddNeo.SubItems.Add("当月总进货量：");
            listToAddNeo.SubItems.Add(numSum.ToString());
            listToAddNeo.SubItems.Add("当月总交易额：");
            listToAddNeo.SubItems.Add(sumSum.ToString());
            In.Items.Add(listToAddNeo);
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }

        private void btnSearchIn_Click(object sender, EventArgs e)//查询按钮点击事件
        {
            SearchLimit searchLimit = new SearchLimit("ID");
            searchLimit.ShowDialog();
            int limitID=-1;
            if (MyOtherUtils.checkInt(searchLimit.returnByName()))//获取文本输入判断是否是整数
            {
                limitID = searchLimit.returnByID();//是整数则进行接收
            }
            if (limitID != -1)//判断是否接收到
            {
                In.Items.Clear();
                String[] tbStrings = { "inID", "itemID", "inprice", "innum", "insum", "inroot", "indate" };
                String sql = String.Format("select * from inlist where itemID={0}", limitID);
                SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection();
                SqlCommand commandLogin = new SqlCommand(sql, connectionLogin);
                SqlDataReader smdr = commandLogin.ExecuteReader();
                int numSum = 0;
                double sumSum = 0;
                while (smdr.Read())
                {
                    ListViewItem listToAdd = new ListViewItem();
                    listToAdd.Text = smdr[tbStrings[0]].ToString();
                    for (int i = 1; i < In.Columns.Count; i++)
                    {
                        listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                    }
                    numSum += Convert.ToInt32(smdr["innum"]);
                    sumSum += Convert.ToDouble(smdr["insum"]);
                    In.Items.Add(listToAdd);
                }
                try
                {
                    connectionLogin.Close();
                }
                catch (Exception exception)
                {
                    MessageBox.Show(exception.StackTrace);
                }
            }
        }

        private void btnReset_Click(object sender, EventArgs e)//重置进货记录
        {
            fillIn();
        }
    }
}
